package org.edu.modules.kwgl.utils;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 * excel相关处理工具类
 * 2020-05-6
 * by cyn
 */
public class ExcelUtils {
    /**
     * 获取当前日期是星期几<br>
     *
     * @param date
     * @return 当前日期是星期几
     */
    public static String getWeekOfDate(Date date) {
        String[] weekDays = { "星期日", "星期一", "星期二", "星期三", "星期四", "星期五", "星期六" };
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        int w = cal.get(Calendar.DAY_OF_WEEK) - 1;
        if (w < 0)
            w = 0;
        return weekDays[w];
    }

    /**
     * 合并单元格处理,获取合并行
     *
     * @param sheet
     * @return List<CellRangeAddress>
     */
    public static List<CellRangeAddress> getCombineCell(Sheet sheet) {
        List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();
        // 获得一个 sheet 中合并单元格的数量
        int sheetmergerCount = sheet.getNumMergedRegions();
        // 遍历合并单元格
        for (int i = 0; i < sheetmergerCount; i++) {
            // 获得合并单元格加入list中
            CellRangeAddress ca = sheet.getMergedRegion(i);
            list.add(ca);
        }
        return list;
    }

    /**
     * 判断单元格是否为合并单元格，是的话则将单元格的值返回
     *
     * @param listCombineCell
     *            存放合并单元格的list
     * @param cell
     *            需要判断的单元格
     * @param sheet
     *            sheet
     * @return
     */
    public static boolean isCombineCell(List<CellRangeAddress> listCombineCell,
                                        Cell cell, Sheet sheet) throws Exception {
        int firstC = 0;
        int lastC = 0;
        int firstR = 0;
        int lastR = 0;
        for (CellRangeAddress ca : listCombineCell) {
            // 获得合并单元格的起始行, 结束行, 起始列, 结束列
            firstC = ca.getFirstColumn();
            lastC = ca.getLastColumn();
            firstR = ca.getFirstRow();
            lastR = ca.getLastRow();
            if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
                if (cell.getColumnIndex() >= firstC
                        && cell.getColumnIndex() <= lastC) {
                    return true;
                }
            }
        }
        return false;
    }


    /**
     * 获取合并单元格的值,当前只需要数值类型
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {

                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return fCell.getStringCellValue();
                }
            }
        }

        return null;
    }


//    /**
//     * 获取获取文本对应性别值
//     *
//     * @param sex
//     * @return
//     */
//    public  static Integer GetSex(String sex){
//        if(StringUtils.isEmpty(sex)){
//            return RedConstant.SEX_VALUE_OTHER;
//        }
//        if(sex.equals(RedConstant.SEX_MALE)) {
//            return RedConstant.SEX_VALUE_MALE;
//        }if(sex.equals(RedConstant.SEX_FEMALE)){
//            return RedConstant.SEX_VALUE_FEMALE;
//        }
//        return RedConstant.SEX_VALUE_OTHER;
//    }



    /**
     * 处理返回结果
     * @param cell
     * @return
     */
    public static String GetStringValue(Cell cell){
        if(cell==null){
            return null;
        }
        switch (cell.getCellType()){
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_NUMERIC:
                DecimalFormat df = new DecimalFormat();
                return df.format(cell.getNumericCellValue()).replace(",","");
        }
        return null;
    }

    public static String GetFlagValue(Cell cell){
        String value=GetStringValue(cell);
        if(value!=null){
            return value.equals("1") ?
                    "1":
                    value.equals("0") ? "0":null;
        }
        return null;
    }



    public static Date GetDateValue(Cell cell){
        if(cell==null){
            return null;
        }
        switch (cell.getCellType()){
            case Cell.CELL_TYPE_STRING:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                }
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                }
        }
        return null;
    }
}
